package com.ztesoft.ossup.report.service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ztesoft.ossup.report.dao.ReportRepository;
import com.ztesoft.ossup.report.dao.ReportSubRepo;
import com.ztesoft.ossup.report.domain.Report;
import com.ztesoft.ossup.report.domain.ReportSub;

@Service("reportConfigService")
public class ReportConfigService {
	@Autowired
	JdbcTemplate jdbcTemplate;

	@Autowired
	ReportRepository reportRepository;

	@Autowired
	ReportSubRepo reportSubRepo;
	
	@Autowired
	DataPreviewService dataPreviewService;

	@SuppressWarnings("unchecked")
	@Transactional
	public Report saveReport(Map<String, Object> params) throws Exception {
		Report report = getReport(params);
		report.setCreateDate(new Date());
		Report reportReturn = reportRepository.save(report);
		if (reportReturn == null) {
			return null;
		}
		// 没有报表的图表类型
		if (params.get("reportType") == null) {
			return reportReturn;
		}
		List<String> reportTypes = new ArrayList<>();
		try {
			reportTypes = (List<String>) params.get("reportType");
		} catch (Exception e) {
			// TODO: handle exception
			reportTypes.add(String.valueOf(params.get("reportType")));
		}

		List<ReportSub> list = new ArrayList<>();
		for (int i = 0; i < reportTypes.size(); i++) {
			String[] type = reportTypes.get(i).split("\\|\\$\\|");
			ReportSub reportSub = new ReportSub();
			reportSub.setType(type[0]);
			reportSub.setName(type[1]);
			reportSub.setReport(reportReturn);
			ReportSub reportSubReturn = reportSubRepo.save(reportSub);
			if (reportSubReturn == null) {
				continue;
			} else {
				list.add(reportSubReturn);
			}
		}
		return reportReturn;
	}

	@SuppressWarnings("unchecked")
	@Transactional
	public Report updateConfig(Map<String, Object> params, Long id) throws Exception {
		Report report = getReport(params);
		report.setId(id);
		//Report reportReturn = reportRepository.save(report);
		reportRepository.updateConfigReportById(report.getIsSum(), report.getName(), report.getSql(), report.getId());
		// 删除已有图表类型重新添加
		reportSubRepo.deleteByReportId(report.getId());
		// 没有报表的图表类型
		if (params.get("reportType") == null) {
			return report;
		}
		List<String> reportTypes = new ArrayList<>();
		try {
			reportTypes = (List<String>) params.get("reportType");
		} catch (Exception e) {
			// TODO: handle exception
			reportTypes.add(String.valueOf(params.get("reportType")));
		}
		List<ReportSub> reportSubs = new ArrayList<>();
		for (int i = 0; i < reportTypes.size(); i++) {
			String[] type = reportTypes.get(i).split("\\|\\$\\|");
			ReportSub sub = getReportSub(type, report);
			reportSubs.add(sub);
		}
		reportSubRepo.save(reportSubs);

		return report;
	}

	public boolean isExistConfigCode(Map<String, String> params) {
		List<Report> report = new ArrayList<>();
		try {
			String code = params.get("code");
			report = reportRepository.findByCode(code);
			if ("add".equals(params.get("type"))) {
				// 添加的时候唯一性判断
				if (report == null || report.size() == 0) {
					return false;
				}
			} else if ("edit".equals(params.get("type"))) {
				// 编辑的时候唯一性判断
				Long id = Long.parseLong(params.get("id"));
				if (report == null || report.size() == 0) {
					return false;
				} else if (report.size() == 1) {
					// 若不是本身，则返回已存在
					if (report.get(0).getId() == id) {
						return false;
					}
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}

	/**
	 * sql检验
	 * 
	 * @param sql
	 * @return
	 */
	public boolean isSqlRight(String sql) {
		// TODO Auto-generated method stub
		if ("".equals(sql)) {
			return false;
		}
		String executeSql = new String(sql);
		if (sql.contains("where")) {
			// 按条件分割
			String[] strings = sql.split("where");
			for (int i = 0; i < strings.length; i++) {

				String str = strings[i];
				// 包含and的条件，即从第一个and开始到下一个where
				if (str.contains("and")) {
					// and划分每一个条件
					int beLeftCondition = 0;
					String[] conditions = str.split("and");
					for (int j = 0; j < conditions.length; j++) {
						String condition = conditions[j];
						if (condition.contains("#")) {
							if (j == conditions.length - 1) {
								int orderByIndex = condition.indexOf("order by");
								int groupByIndex = condition.indexOf("group by");
								String oldString = "";
								if (beLeftCondition > 0 && !conditions[j - 1].contains("#")) {
									condition = "and" + condition;
								}

								if (orderByIndex < 0 && groupByIndex < 0) {
									executeSql = executeSql.replace(condition, " ");
								} else {
									if (orderByIndex > 0 && groupByIndex > 0) {
										oldString = condition.substring(0, Math.min(orderByIndex, groupByIndex));
									} else {
										oldString = condition.substring(0, Math.max(orderByIndex, groupByIndex));
									}
									executeSql = executeSql.replaceFirst(oldString, " ");
								}
							} else {
								executeSql = executeSql.replace(condition + "and", " ");
							}
						} else {
							beLeftCondition++;
						}
					}
					if (beLeftCondition > 0) {
						continue;
					} else {
						executeSql = executeSql.replaceFirst("where", " ");
					}
				} else if (str.contains("#")) {
					int orderByIndex = str.indexOf("order by");
					int groupByIndex = str.indexOf("group by");
					if (orderByIndex > 0 || groupByIndex > 0) {
						String oldString = str.substring(0, Math.min(orderByIndex, groupByIndex));
						sql.replace(oldString, " ");
					} else {
						sql.replace(str, " ");
					}
				}
			}

		}

		// 执行sql 报异常则验证不通过
		try {
			jdbcTemplate.execute(executeSql);
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}
	
	public boolean checkSql(String sql){
		String executeSql = dataPreviewService.doSqlNoParams(sql);
		//封装代码 未避免查询数量太多，影响内存数据
		executeSql = "select * from (" + executeSql + ") tttt where 1 > 0";
		try {
			jdbcTemplate.execute(executeSql);
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}

	// List<Integer> oddIndex = new ArrayList<>();
	// List<Integer> evenIndex = new ArrayList<>();
	// boolean isOdd = true;
	// for (int i = 0; i < sql.length(); i++) {
	// if (sql.substring(i, i + 1).equals("#")) {
	// if (isOdd) {
	// oddIndex.add(i);
	// }else {
	// evenIndex.add(i);
	// }
	// isOdd = !isOdd;
	// }
	// }
	//
	// for (int i = 0; i < oddIndex.size(); i++) {
	// String key=sql.substring(oddIndex.get(i), evenIndex.get(i)+1);
	//
	// }

	public Report getReport(Map<String, Object> params) {
		// TODO Auto-generated constructor stub
		Report report = new Report();
		if (params.get("code") != null && !params.get("code").equals("")){
			report.setCode(String.valueOf(params.get("code")));
		}
		report.setName(String.valueOf(params.get("name")));
		report.setIsSum(String.valueOf(params.get("isSum")));
		report.setSql(String.valueOf(params.get("sql")));
		
		report.setStateDate(new Date());
		report.setState("10A");
		return report;
	}

	public ReportSub getReportSub(String[] type, Report report) {

		ReportSub reportSub = new ReportSub();
		reportSub.setType(type[0]);
		reportSub.setName(type[1]);
		reportSub.setReport(report);
		return reportSub;
	}

}
